package org.JDBC;

// 添加必要的导入语句
import org.junit.Test;
import java.sql.*;

public class DQLTest {

    private String driverClass = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://127.0.0.1:3306/briup";
    private String user = "root";
    private String password = "1234";

    @Test
    public void select() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.加载注册驱动
            Class.forName(driverClass);
            //2.获取连接对象
            conn = DriverManager.getConnection(url,user,password);
            //3.获取Statement对象
            stmt = conn.createStatement();
            //4.执行SQL语句
            String sql = "select id,name,age from t_user";
            rs = stmt.executeQuery(sql);
            //5.处理结果集
            while(rs.next()) {
                //5.1 使用列的序号获取值，从1开始
                //long id = rs.getInt(1);
                //String name = rs.getString(2);
                //int age = rs.getInt(3);
                //5.2 使用列的名字获取值
                long id = rs.getLong("id"); // 使用getLong更适合id
                String name = rs.getString("name");
                int age = rs.getInt("age");
                System.out.println("id：" + id + ", name：" + name + ", age：" + age);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if(rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    // 添加查询特定用户的方法
    @Test
    public void selectById() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            //1.加载注册驱动
            Class.forName(driverClass);
            //2.获取连接对象
            conn = DriverManager.getConnection(url,user,password);
            //3.获取PreparedStatement对象
            String sql = "select id,name,age from t_user where id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setLong(1, 1); // 查询id为1的用户
            //4.执行SQL语句
            rs = stmt.executeQuery();
            //5.处理结果集
            if(rs.next()) {
                long id = rs.getLong("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                System.out.println("查询到用户 - id：" + id + ", name：" + name + ", age：" + age);
            } else {
                System.out.println("未找到指定用户");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //6.释放资源
            if(rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}